Imports BusinessLogicLayer
Imports BusinessLogicLayer.BLMain
Imports BusinessLogicLayer.BLDisposer
Imports DataAccessLayer.DLMain
Imports UniversalLayer
Imports UniversalLayer.ULMain
Imports UniversalLayer.ULConverter
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop

''' <summary>
''' NO COMMENTS
''' </summary>
''' <remarks></remarks>
Public Class DLWriter

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum eTransactSQL
        Delete = 0 '1 --> 0
        Update = 1 '1 --> 1
        Insert = 2 '1 --> 2
    End Enum

    'Dim _DLMain As DLMain
    'Dim _DLReader As DLReader
    'Dim _DLCon As New DLConnector
    Dim con As SqlClient.SqlConnection

    Public Sub New(ByVal sMSG As String)
        Dim _DLConnector As DLConnector
        Try
            _DLConnector = New DLConnector
            _DLConnector.CreateConnection(con, sMSG) 'Open connection
        Finally
            _DLConnector = Nothing
        End Try
    End Sub

    Protected Overrides Sub Finalize()
        DLConnector.CloseConnection(con)
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' NOT COMPLETE
    ''' </summary>
    ''' <param name="oMuvee"></param>
    ''' <remarks></remarks>
    Public Sub AddNew(ByVal oMuvee As DLEntertainment)

        'If _BLMain.Validate(oMuvee) Then
        '    'Add New
        'End If

        MsgBox("yet to add AddNew code")
        Exit Sub

    End Sub

    ''' <summary>
    ''' NOT COMPLETE
    ''' </summary>
    ''' <param name="oMuvee"></param>
    ''' <remarks></remarks>
    Public Sub Save(ByVal oMuvee As DLEntertainment)

        'If _BLMain.Validate(oMuvee) Then
        '    'Save
        'End If

        MsgBox("yet to add Save code")
        Exit Sub

    End Sub

#Region "Execute SQL statements"

    ''' <summary>
    ''' Executes a SQL query to perform Insert/Update/Delete operations
    ''' </summary>
    ''' <param name="sSQL">INSERT / UPDATE / DELETE statement</param>
    ''' <param name="iTransactSQL">Transaction type: INSERT / UPDATE / DELETE</param>
    ''' <param name="bConfirmation">TRUE : notifies user about total no. of rows affected</param>
    ''' <returns>No. of rows affected</returns>
    ''' <remarks></remarks>
    Public Function ExecuteTransactSQL(ByVal sSQL As String, ByVal iTransactSQL As eTransactSQL, Optional ByVal bConfirmation As Boolean = False) As Integer
        'Try
        '    'Open connection
        '    Dim con As SqlClient.SqlConnection = Nothing
        '    _DLCon.CreateConnection(con)

        '    'Fetch records from db
        '    Dim da As New SqlClient.SqlDataAdapter(sSQL, con)
        '    Dim ds As New DataSet
        '    da.Fill(ds, sTableName)

        '    'Populate the DataTable
        '    dtTarget = Nothing
        '    dtTarget = ds.Tables(sTableName)

        '    'Close connection
        '    _DLCon.CloseConnection(con)

        'Catch ex As Exception
        '    dtTarget = Nothing
        'End Try

        '
        Dim ra As Integer 'no. of recs inserted

        'Dim myConn As SqlConnection
        '_DLCon.CreateConnection(myConn)
        'Dim myComm As SqlCommand
        'myComm = New SqlCommand(sSQL, myConn)
        'ra = myComm.ExecuteNonQuery
        '_DLCon.CloseConnection(myConn)

        'Dim con As SqlConnection = Nothing
        '_DLCon.CreateConnection(con)
        Dim da As New SqlDataAdapter
        Select Case iTransactSQL
            Case eTransactSQL.Insert
                da.InsertCommand = New SqlCommand(sSQL, con)
                ra = da.InsertCommand.ExecuteNonQuery()

            Case eTransactSQL.Update
                da.UpdateCommand = New SqlCommand(sSQL, con)
                ra = da.UpdateCommand.ExecuteNonQuery()

            Case eTransactSQL.Delete
                da.DeleteCommand = New SqlCommand(sSQL, con)
                ra = da.DeleteCommand.ExecuteNonQuery()
        End Select

        If bConfirmation Then _
        MsgBox(iTransactSQL.ToString & "ed " & ra.ToString & " row" & IIf(ra > 1, "s", "").ToString, , iTransactSQL.ToString.ToUpper & " complete..")

        '_DLCon.CloseConnection(con)
        Return ra
    End Function

#End Region

#Region "Write to File"

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="dtCSV"></param>
    ''' <param name="sParentDirectory"></param>
    ''' <param name="sFileName"></param>
    ''' <remarks> <!-- URLs
    ''' https://www.google.com/search?hl=en&safe=off&biw=1024&bih=641&noj=1&gbv=2&sclient=psy-ab&q=write+csv+.net+database&oq=write+csv+.net+database&aq=f&aqi=&aql=&gs_sm=3&gs_upl=55037l64989l0l65162l23l19l0l0l0l0l875l875l6-1l1l0&gs_l=serp.3...55037l64989l0l65162l23l19l0l0l0l0l875l875l6-1l1l0.frgbld.
    ''' http://www.codeproject.com/Articles/11435/Importing-CSV-Data-and-saving-it-in-database
    ''' http://technet.microsoft.com/en-us/library/dd347724.aspx
    ''' http://dotnetguts.blogspot.in/2007/01/exporting-datatable-to-csv-file-format.html -->
    ''' </remarks>
    Public Sub WriteCSV(ByVal dtCSV As DataTable, ByVal sParentDirectory As String, ByVal sFileName As String)
        Dim sFullFilePath As String
        Dim _DLMain As DLMain
        Dim oWriter As IO.StreamWriter

        Try
            _DLMain = New DLMain
            sFullFilePath = IO.Path.GetFileNameWithoutExtension(sParentDirectory.TrimEnd("\"c) & sFileName) & ".csv"
            If Not ULMain.IsDirectoryExists(sParentDirectory) Then IO.Directory.CreateDirectory(sParentDirectory)
            'if not _BLMain.isFileExists(sParentDirectory.TrimEnd("\"c) & sFileName) then io.File.

            oWriter = IO.File.CreateText(sFullFilePath)
            oWriter.WriteLine(_DLMain.ConvertToCSV(dtCSV))

        Finally
            oWriter.Close()
            oWriter = Nothing
            sFullFilePath = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sPath"></param>
    ''' <param name="dtTableXML"></param>
    ''' <remarks></remarks>
    Public Sub WriteXML(ByVal sPath As String, ByRef dtTableXML As DataTable)
        Try
            If Not ULMain.IsEmpty(dtTableXML) Then
                'dtTable.WriteXmlSchema("~/DataTable.xml")
                'dtTable.WriteXml("~/DataTable.xml", XmlWriteMode.WriteSchema)
            End If
        Finally

        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sPath"></param>
    ''' <param name="dtTableDOC"></param>
    ''' <remarks></remarks>
    Public Sub WriteDOC(ByVal sPath As String, ByRef dtTableDOC As DataTable)

    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sPath"></param>
    ''' <param name="dtTablePDF"></param>
    ''' <remarks></remarks>
    Public Sub WritePDF(ByVal sPath As String, ByRef dtTablePDF As DataTable)

    End Sub

    ''' <summary>
    ''' Writes specified data into Excel format (xls, xlsx)
    ''' </summary>
    ''' <param name="sFilePath">Location of Excel file</param>
    ''' <param name="dtTableXLS">DataTable containing source data</param>
    ''' <param name="iEntertainmentType">Entertainment type</param>
    ''' <remarks></remarks>
    Public Sub WriteXLS(ByVal sFilePath As String, ByRef dtTableXLS As DataTable, ByVal iEntertainmentType As eEntertainmentType)
        Dim sExtension As String = IO.Path.GetExtension(sFilePath)
        Dim _excel As Excel.Application
        Dim _wBook As Excel.Workbook
        Dim _wSheet As Excel.Worksheet

        _excel = New Excel.ApplicationClass
        _wBook = _excel.Workbooks.Add
        _wSheet = CType(_wBook.ActiveSheet, Excel.Worksheet)
        _wSheet.Name = iEntertainmentType.ToString

        Select Case sExtension.ToUpper
            Case "XLS", "XLSX"
                Dim iCol, iRow As Integer
                For Each dc As DataColumn In dtTableXLS.Columns
                    iCol += 1
                    _excel.Cells(1, iCol) = dc.ColumnName
                    '_excel.Cells(1, iCol)
                    '_Excel.Rows.EntireRow.
                Next
                For Each dr As DataRow In dtTableXLS.Rows
                    iRow += 1
                    iCol = 0
                    For Each dc As DataColumn In dtTableXLS.Columns
                        iCol += 1
                        _excel.Cells(iRow + 1, iCol) = dr(dc.ColumnName)
                    Next
                Next

                _wSheet.Columns.AutoFit()
                'Dim _Writer As New System.IO.StreamWriter(sFileName)
                _wBook.SaveAs(sFilePath)
                _excel.Workbooks.Open(sFilePath)
                _excel.Visible = True
                '_wBook.Close()
                _excel.Quit()
                ReleaseObject(_excel)
                ReleaseObject(_wBook)
                ReleaseObject(_wSheet)
        End Select
    End Sub

#End Region








    ''' <summary>
    ''' Logs error into DB
    ''' </summary>
    ''' <param name="ex">Exception instance</param>
    ''' <param name="sInitiatorMethod">Method name which initiated the process causing the error</param>
    ''' <param name="sExceptionSource">Method name that raised the error</param>
    ''' <param name="sNotesComments">Notes and Comments on error, as entered by user</param>
    ''' <remarks></remarks>
    Public Sub LogErrorToDB(ByVal ex As Exception, ByVal sInitiatorMethod As String, ByVal sExceptionSource As String, ByVal sNotesComments As String)
        Dim sSQL As String
        Try
            sSQL = "INSERT INTO ErrorLogMain (ErrInitiator, ErrSource, ErrMessage, ErrTargetSite, ErrDescription, ErrStackTrace, ErrComments) " & _
                   "VALUES ('" & CleanSQL(sInitiatorMethod) & "', '" & CleanSQL(sExceptionSource) & "', '" & CleanSQL(ex.Message) & "', '" & CleanSQL(ex.TargetSite.ToString) & "', '" & CleanSQL(ex.ToString) & "', '" & CleanSQL(ex.StackTrace) & "', '" & CleanSQL(Left(sNotesComments, 1000)) & "')"
            ExecuteTransactSQL(sSQL, eTransactSQL.Insert)
        Finally
            sSQL = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Add record to Watchlist
    ''' </summary>
    ''' <param name="sEntertainmentMainPKey">EntertainmentMain.PKey</param>
    ''' <remarks></remarks>
    Public Sub WatchlistAdd(ByVal sEntertainmentMainPKey As String)
        Dim sSQL As String
        Dim dtWatchlist As New DataTable
        Dim _DLReader As New DLReader("DLWriter")

        Try
            _DLReader.ExecuteSQL("SELECT * FROM WatchlistView WHERE EntertainmentMainPKey = '" & sEntertainmentMainPKey & "' AND Seen = 2 ORDER BY DateAdded DESC", dtWatchlist)

            If dtWatchlist.Rows.Count = 0 Then
                'sSQL = "INSERT INTO WatchlistMain(EntertainmentMainPKey, DateAdded) VALUES('" & sEntertainmentMainPKey & "', '" & Now.Date & "')"
                sSQL = "INSERT INTO WatchlistMain(EntertainmentMainPKey, DateAdded) VALUES(" & sEntertainmentMainPKey & ", convert(varchar,current_timestamp,110))"

                ''Else
                ''    If btnWatchlistAdd.Enabled Then MessageBox.Show("The item has already been added into watchlist" & vbCrLf & "on " & Format(CDate(dtWatchlist.Rows(0).Item("DateAdded")), "dd-MMM-yyyy"), "Watchlist", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ''    btnWatchlistAdd.Enabled = False
            End If
            If Not ULMain.IsEmpty(sSQL) Then Call ExecuteTransactSQL(sSQL, DLWriter.eTransactSQL.Insert)

        Finally
            sSQL = Nothing
            _DLReader = Nothing
            dtWatchlist = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sCSVFullPath"></param>
    ''' <remarks></remarks>
    Public Sub WatchlistUpdateToDB(ByVal sCSVFullPath As String)
        Dim _DLMain As DLMain
        Dim _DLReader As DLReader
        Dim sUpdateSQL As String
        Dim dtWatchlist As New DataTable
        Dim iUpdateCounter As Integer = 0

        Try
            _DLMain = New DLMain
            _DLReader = New DLReader("DLWriter")
            _DLReader.ReadWatchlistCSV(sCSVFullPath, dtWatchlist)

            ''Add columns to final table
            'Dim dtUpdate As New DataTable
            'For Each sColumn As String In DLMain.sWatchlistColumns.Split(","c)
            '    dtUpdate.Columns.Add(sColumn)
            'Next

            'Add data to final table
            'Dim drUpdate As DataRow
            For Each drWatchlist As DataRow In dtWatchlist.Rows
                'drUpdate = dtUpdate.NewRow
                'For Each sColumnName As String In DLMain.sWatchlistColumns.Split(","c)
                '    drUpdate(sColumnName) = drWatchlist(sColumnName)
                'Next
                'dtUpdate.Rows.Add(drUpdate)

                'RKD: PENDING_20120524: update_into_TEMP_table_AND_import_data_from_table_drop_table_OR_use_batch_id_for_each_import()
                sUpdateSQL = "UPDATE EntertainmentMain SET IMDbRating='" & drWatchlist("IMDb Rating").ToString & "', CustomRating='" & drWatchlist("You rated").ToString & "' WHERE IMDbCode='" & drWatchlist("const").ToString & "' AND IMDbRating <> '" & drWatchlist("IMDb Rating").ToString & "'"
                Call ExecuteTransactSQL(sUpdateSQL, eTransactSQL.Update)
                iUpdateCounter += 1
                sUpdateSQL = Nothing
            Next

            'For Each dc As DataColumn In dtWatchlist.Columns
            '    If Not _BLMain.InArray(DLMain.sWatchlistColumns, dc.ColumnName) Then _
            '        dtWatchlist.Columns.Remove(dc)
            'Next

            'For Each dr As DataRow In dtWatchlist.Rows
            '    sUpdateSQL = "UPDATE EntertainmentMain SET IMDbRating=" & dr("IMDb Rating").ToString & ",CustomRating=" & dr("You rated").ToString & " WHERE IMDbCode='" & dr("const").ToString & "'"
            '    _DLMain.ExecuteTransactSQL(sUpdateSQL, DLMain.eTransactSQL.Update)
            '    sUpdateSQL = Nothing
            'Next

            MsgBox(CStr(iUpdateCounter) & " records updated successfully..", , "IMD Watchlist update")

        Finally
            _DLMain = Nothing
            _DLReader = Nothing
            sUpdateSQL = Nothing
            dtWatchlist = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sFullFilePath"></param>
    ''' <remarks></remarks>
    Public Sub WriteFileToDB(ByVal sFullFilePath As String)
        Dim myFile As IO.FileInfo
        Dim len As Long
        Dim fs As IO.FileStream
        Dim img() As Byte
        Dim iBytesRead As Integer

        Try
            myFile = New IO.FileInfo(sFullFilePath)
            len = myFile.Length
            fs = New IO.FileStream(sFullFilePath, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read)
            img(CInt(len)) = New Byte
            iBytesRead = fs.Read(img, 0, CInt(len))
        Finally
            myFile = Nothing
            len = Nothing
            fs = Nothing
            Erase img
            iBytesRead = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="pkUserMainPKey"></param>
    ''' <remarks></remarks>
    Public Sub IncrementLoginCount(ByVal pkUserMainPKey As Integer)
        ExecuteTransactSQL("UPDATE UserMain SET LoginCount = LoginCount + 1 WHERE PKey = '" & pkUserMainPKey & "'", eTransactSQL.Update)
    End Sub

    ''' <summary>
    ''' Resets user password after validation
    ''' </summary>
    ''' <param name="sDefaultPswd">Default Password defined by admin</param>
    ''' <param name="dtPasswordRetrieval">DataTable containing password retrieval details</param>
    ''' <returns>No. of rows affected</returns>
    ''' <remarks></remarks>
    Public Function ResetUser(ByVal sDefaultPswd As String, ByVal dtPasswordRetrieval As DataTable) As Integer
        'RKD : 20120610 :: Dim _BLSecMgmt As BLSecurityMgmt
        Dim iRowCount As Integer

        Try
            'RKD : 20120610 :: _BLSecMgmt = New BLSecurityMgmt
            'RKD : 20120610 :: iRowCount = ExecuteTransactSQL("UPDATE UserMain SET UserPassword = '" & CleanSQL(_BLSecMgmt.Encryptor(sDefaultPswd)) & "', NeedsPswdChng = 1 WHERE PKey = '" & dtPasswordRetrieval.Rows(0).Item("PKey").ToString & "' AND Status <> 'd'", eTransactSQL.Update)
            Return iRowCount
        Finally
            'RKD : 20120610 :: _BLSecMgmt = Nothing
            iRowCount = Nothing
        End Try
    End Function

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sUserName"></param>
    ''' <remarks></remarks>
    Public Sub CancelDefaultPasswordChange(ByVal sUserName As String)
        Dim dtUserDetails As New DataTable
        Dim _DLReader As New DLReader("DLWriter")
        Dim _DLSettings As New DLSettings
        Dim iAllowedSkips As Integer = CInt(_DLSettings.FetchOneSetting("UserMgmt", "AllowedChngDefSkips"))

        Try
            _DLReader.FetchUserDetails(sUserName, dtUserDetails, "NeedsPswdChng <> " & iAllowedSkips.ToString)
            If dtUserDetails.Rows.Count <> 0 Then ExecuteTransactSQL("UPDATE UserMain SET NeedsPswdChng = NeedsPswdChng + 1 WHERE PKey ='" & CStr(dtUserDetails.Rows(0).Item("PKey")) & "' AND Status <> 'd'", eTransactSQL.Update)
        Finally
            dtUserDetails = Nothing
            _DLReader = Nothing
            _DLSettings = Nothing
            iAllowedSkips = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS
    ''' </summary>
    ''' <param name="sUserName"></param>
    ''' <remarks></remarks>
    Public Sub ChangeDefaultPassword(ByVal sUserName As String)
        Dim dtUserDetails As New DataTable
        Dim _DLReader As New DLReader("DLWriter")

        Try
            _DLReader.FetchUserDetails(sUserName, dtUserDetails, "")
            ExecuteTransactSQL("UPDATE UserMain SET NeedsPswdChng = 0 WHERE PKey ='" & CStr(dtUserDetails.Rows(0).Item("PKey")) & "' AND Status <> 'd'", eTransactSQL.Update)
        Finally
            _DLReader = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' NO COMMENTS : Adds new user after validation
    ''' </summary>
    ''' <param name="sUserName"></param>
    ''' <param name="sPassword"></param>
    ''' <param name="sSecQues"></param>
    ''' <param name="sSecAns"></param>
    ''' <remarks></remarks>
    Public Sub RegisterUser(ByVal sUserName As String, ByVal sPassword As String, ByVal sSecQues As String, ByVal sSecAns As String)
        'Add new user in UserMain and to approval queue as 'Pending'
        'RKD : 20120610 :: Dim _BLSecMgmt As BLSecurityMgmt
        Dim sNewUserSQL As String

        Try
            'RKD : 20120610 :: _BLSecMgmt = New BLSecurityMgmt
            'RKD : 20120610 :: sNewUserSQL = "IF NOT EXISTS (SELECT 1 FROM UserDetailsView WHERE UserName = '" & sUserName & "') " & _
            'RKD : 20120610 ::               "BEGIN " & _
            'RKD : 20120610 ::               "INSERT INTO UserMain (UserName, UserPassword, SecQues, SecAns) " & _
            'RKD : 20120610 ::               "VALUES('" & sUserName & "', '" & _BLSecMgmt.Encryptor(sPassword) & "', '" & sSecQues & "', '" & sSecAns & "'); " & _
            'RKD : 20120610 ::               "INSERT INTO ApprovalMain(UserMainPKey, ApprovalState) SELECT PKey, 'Pending' FROM UserDetailsView WHERE UserName = '" & sUserName & "'; " & _
            'RKD : 20120610 ::               "END"
            ExecuteTransactSQL(sNewUserSQL, eTransactSQL.Insert)
        Finally
            'RKD : 20120610 :: _BLSecMgmt = Nothing
            sNewUserSQL = Nothing
        End Try
    End Sub
End Class
